/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package databeans;

import beans.AlumnoBean;
import com.sun.rowset.CachedRowSetImpl;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.sql.rowset.CachedRowSet;
import utils.CargarConfiguraciones;

/**
 *
 * @author Emerson
 */
public class AlumnoDataBean {
    private CachedRowSet rowSet;
    private CargarConfiguraciones cc;
    private int pagesize;

    public AlumnoDataBean()throws Exception{
        cc = new CargarConfiguraciones();
        Class.forName(cc.getValor("driver"));
        rowSet = new CachedRowSetImpl();
        rowSet.setUrl(cc.getValor("urlbase"));
        rowSet.setUsername(cc.getValor("usuario"));
        rowSet.setPassword(cc.getValor("clave"));
        pagesize = Integer.parseInt(cc.getValor("paginacion"));
    }

    public void setAlumno (AlumnoBean alumno) throws SQLException{
        String sql="INSERT INTO alumno (" +
                        "carnet, " +
                        "nombre, " +
                        "sexo, " +
                        "email, " +
                        "telefono)" +
                "VALUES (" +
                        "'" + alumno.getCarnet() + "'," +
                        "'" + alumno.getNombre() + "', " +
                        "'" + alumno.getSexo() + "', " +
                        "'" + alumno.getEmail() + "', " +
                        "'" + alumno.getTelefono() + "')";
        rowSet.setCommand(sql);
        rowSet.execute();

    }
    public  ArrayList<AlumnoBean> getListaAlumnos() throws SQLException{
ArrayList<AlumnoBean> listaAlumnos = new ArrayList<AlumnoBean>();
String sql= "select " +
            "CARNET, " +
            "NOMBRE, " +
            "SEXO, " +
            "EMAIL, " +
            "TELEFONO " +
            "from alumno";

rowSet.setCommand(sql);
rowSet.execute();
rowSet.beforeFirst();
    while (rowSet.next()) {
       AlumnoBean alumno= new AlumnoBean();
        alumno.setCarnet(rowSet.getString(1));
        alumno.setNombre(rowSet.getString(2));
        alumno.setSexo(rowSet.getString(3));
        alumno.setEmail(rowSet.getString(4));
        alumno.setTelefono(rowSet.getString(5));

        listaAlumnos.add(alumno);
    }
return listaAlumnos;
}
    public int getConteoAlumnos() throws SQLException{
        String sql = "SELECT COUNT(*) FROM alumno";
        rowSet.setCommand(sql);
        rowSet.execute();
        rowSet.beforeFirst();
        rowSet.next();
        return rowSet.getInt(1);
    }

    public  ArrayList<AlumnoBean> getListaAlumnos(int pag) throws SQLException{
ArrayList<AlumnoBean> listaAlumnos = new ArrayList<AlumnoBean>();
String sql= "select " +
            "CARNET, " +
            "NOMBRE, " +
            "SEXO, " +
            "EMAIL, " +
            "TELEFONO " +
            "from alumno " +
            "limit "+ pagesize*pag +", "+pagesize;
System.out.println(sql);
rowSet.setCommand(sql);
rowSet.execute();
rowSet.beforeFirst();
    while (rowSet.next()) {
       AlumnoBean alumno= new AlumnoBean();
        alumno.setCarnet(rowSet.getString(1));
        alumno.setNombre(rowSet.getString(2));
        alumno.setSexo(rowSet.getString(3));
        alumno.setEmail(rowSet.getString(4));
        alumno.setTelefono(rowSet.getString(5));

        listaAlumnos.add(alumno);
    }
return listaAlumnos;
}

    public AlumnoBean getAlumno(String carnet){

        AlumnoBean alumno= new AlumnoBean();

         try{
             String sql=  "select " +
                    "carnet, " +
                    "nombre, " +
                    "sexo, " +
                    "email, telefono from alumno " +
                    "where carnet='" +carnet+"'";

            rowSet.setCommand(sql);
            rowSet.execute();
            rowSet.beforeFirst();
            rowSet.next();

            alumno.setCarnet(rowSet.getString(1));
            alumno.setNombre(rowSet.getString(2));
            alumno.setSexo(rowSet.getString(3));
            alumno.setEmail(rowSet.getString(4));
            alumno.setTelefono(rowSet.getString(5));

        }catch(SQLException e)
        {
            e.printStackTrace();
        }
  return alumno;
    }

}

